package com.szy.web.dao;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.szy.web.model.Page;
import com.szy.web.model.News;
import java.util.List;


public class NewsDAO
{
	 SqlManager manager;
	 static String sql = "";
	 static ResultSet rs;

	public NewsDAO() throws IOException, ClassNotFoundException
	{
		manager = SqlManager.createInstance();
	}
	public  void main(String[] args) {
		showNews(1, 20);
	}

	public  Page showNews(int nid){
		List<News> list = new ArrayList<News>();
		Page page = new Page();
		sql = "SELECT * FROM t_news where nid= ? ";
		Object[] params = new Object[]{ nid };
		News news = new News();
		try {
			manager.connectDB();
			rs = manager.executeQuery(sql,params);
			while (rs.next()) 
			{
				news.setNid(Integer.parseInt(rs.getString("nid")));
				news.setCid(Integer.parseInt(rs.getString("cid")));
				news.setTitle(rs.getString("title"));
				news.setDigest( rs.getString("digest"));
				
				String  newsBody = rs.getString("body");
				newsBody = newsBody.replaceAll("<p>", "");
				newsBody = newsBody.replaceAll("</p>", "");
				news.setBody(newsBody);
				news.setSource(rs.getString("source"));
				news.setPtime(rs.getString("ptime"));
				news.setImgSrc(rs.getString("imgsrc"));
				news.setDeleted(rs.getString("deleted"));
				System.out.println(rs.getString("title"));
				list.add(news);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		}
		page.setTotalRecords( list.size() );
		return page;
	}
	/**
	 * 
	 * @param currentPage
	 * @param pageSize
	 * @return 把分页查询出来的内容放到page里面。
	 * @throws Exception
	 */
	public  Page showNews(int currentPage, int pageSize) {
		List<News> list = new ArrayList<News>();
		Page page = new Page();
		int n = 0;
		int begin =0;
		begin = ( currentPage - 1 ) * pageSize ;
		sql = "SELECT * FROM t_news   ORDER BY deleted = 0 ASC, t_news.nid DESC  LIMIT ?,?  " ;
		Object[] params = new Object[]{begin, pageSize };
		try {
			manager.connectDB();
			rs = manager.executeQuery(sql,params);
			while (rs.next()) {
				News news = new News();
				news.setNid(Integer.parseInt(rs.getString("nid")));
				news.setCid(Integer.parseInt(rs.getString("cid")));
				news.setTitle(rs.getString("title"));
				news.setDigest(rs.getString("digest"));
				news.setBody(rs.getString("body"));
				news.setSource(rs.getString("source"));
				news.setPtime(rs.getString("ptime"));
				news.setImgSrc(rs.getString("imgsrc"));
				news.setDeleted(rs.getString("deleted"));
				list.add(news);
			}
			page.setList(list);
			page.setCurrentPage(currentPage);
			page.setPageSize(pageSize);
			sql = "SELECT * FROM t_news where deleted = 0";
			Object[] params1 = new Object[]{ };
			rs = manager.executeQuery(sql,params1);
			
			while(rs.next()){
				n++;
			}
			page.setTotalRecords(n);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return page;
	}
	
	public void updateNews( int nid ,int cid,String title,String digest,String body,String source,int deleted )
	{
		sql = "update  t_news set cid = ?,title = ? ,digest = ?,body = ?,source = ?,deleted = ? where nid = ? ";
		Object[] params = new Object[]{ cid,title,digest,body,source,deleted,nid };
		try {
			manager.connectDB();
			manager.executeUpdate(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	public void updateNews( int nid ,int cid,String title,String digest,String body,String source, String imgsrc,int deleted )
	{
		sql = "update  t_news set cid = ?,title = ? ,digest = ?,body = ?,source = ?,imgsrc = ?, deleted = ? where nid = ? ";
		Object[] params = new Object[]{ cid,title,digest,body,source,imgsrc,deleted,nid };
		try {
			manager.connectDB();
			manager.executeUpdate(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	
	/**
	 * 获取新闻内容
	 * @param nid 新闻编号
	 * @return
	 * @throws SQLException
	 */
	public News getNews(int nid) throws SQLException
	{
		sql = "SELECT nid,cid,title,body,source,ptime,imgsrc FROM t_news WHERE nid=? and deleted=false";
		Object[] params = new Object[]{ nid };
		manager.connectDB();
		rs = manager.executeQuery(sql, params);
		News news = new News();
		if (rs.next())
		{
			news.setNid(rs.getInt("nid"));
			news.setCid(rs.getInt("cid"));
			news.setTitle(rs.getString("title"));
			news.setBody(rs.getString("body"));
			news.setSource(rs.getString("source"));
			news.setPtime(rs.getString("ptime"));
			news.setImgSrc(rs.getString("imgsrc"));
		}
		manager.closeDB();
		return news;
	}
	public void addNews(int cid,String title,String digest,String body,String source,String ptime,String imgrc) throws SQLException {
		sql = "INSERT INTO t_news (cid,title,digest,body,source,ptime,imgsrc,deleted)VALUES(?, ?,?,?,?,?,?,0)";		
		System.out.println(body);
		
		Object[] params = new Object[]{cid,title,digest,body,source,ptime,imgrc};
		manager.connectDB();;
		manager.executeUpdate(sql, params);
		manager.closeDB();
		
	}
	/**
	 * 获取指定类别的新闻列表
	 * @param tid 新闻类型
	 * @param startNid 起始编号
	 * @param count 返回数量
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<News> getSpecifyCategoryNews(int tid, int startNid, int count)
			throws SQLException
	{
		ArrayList<News> list = new ArrayList<News>();
		sql = "SELECT nid,cid,title,digest,source,ptime,imgsrc FROM t_news WHERE cid=? AND deleted=false ORDER BY ptime desc LIMIT ?,?";
		Object[] params = new Object[]{ tid, startNid, count };
		manager.connectDB();
		rs = manager.executeQuery(sql, params);
		while (rs.next())
		{
			News news = new News();
			news.setNid(rs.getInt("nid"));
			news.setCid(rs.getInt("cid"));
			news.setTitle(rs.getString("title"));
			news.setDigest(rs.getString("digest"));
			news.setSource(rs.getString("source"));
			news.setPtime(rs.getString("ptime"));
			news.setImgSrc(rs.getString("imgsrc"));
			list.add(news);
		}
		manager.closeDB();
		return list;
	}
}

